package com.csu.dao.impl;

import com.csu.dao.BorrowInfoDao;
import com.csu.entiry.Book;
import com.csu.entiry.BorrowInfo;
import com.csu.service.impl.BookServiceImpl;
import com.csu.utils.JDBCUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

public class BorrowInfoDaoImpl implements BorrowInfoDao {
    @Override
    public List<BorrowInfo> getBorrowInfoList(int current, int size, String key) {
        List<BorrowInfo> borrowInfos = new ArrayList<>();
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select borrowid, bookid, bookname, reader, borrowdate, returndate, isreturn from borrowinfo " +
                "where bookname like ? or reader like ? " +
                " limit ?,?";
        if (key == null) {
            key = "";
        }
        try {
            JDBCUtil.begin();
            ps = Objects.requireNonNull(connection).prepareStatement(sql);
            ps.setString(1, "%" + key + "%");
            ps.setString(2, "%" + key + "%");
            ps.setInt(3, (current - 1) * size);
            ps.setInt(4, size);
            rs = ps.executeQuery();
            while (rs.next()) {
                BorrowInfo borrowInfo = new BorrowInfo();
                borrowInfo.setBorrowId(rs.getInt("borrowid"));
                borrowInfo.setBookId(rs.getInt(2));
                borrowInfo.setBookName(rs.getString(3));
                borrowInfo.setReader(rs.getString(4));
                borrowInfo.setBorrowDate(rs.getDate(5));
                borrowInfo.setReturnDate(rs.getDate(6));
                borrowInfo.setIsReturn(rs.getInt(7));
                Date date = new Date(System.currentTimeMillis());
                if(borrowInfo.getIsReturn() == 0 && date.after(borrowInfo.getReturnDate())){
                    borrowInfo.setShouldReturn(true);
                }else{
                    borrowInfo.setShouldReturn(false);
                }
                borrowInfos.add(borrowInfo);
            }
            JDBCUtil.commit();

        } catch (SQLException e) {
            e.printStackTrace();
            JDBCUtil.rollback();
        } finally {
            JDBCUtil.closeAll(connection, ps, rs);
        }
        return borrowInfos;
    }

    @Override
    public int getTotal(int current, int size, String key) {
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select count(1) from borrowinfo " +
                "where bookname like ? or reader like ?";
        if (key == null) {
            key = "";
        }
        int total = 0;
        try {
            JDBCUtil.begin();
            ps = Objects.requireNonNull(connection).prepareStatement(sql);
            ps.setString(1, "%" + key + "%");
            ps.setString(2, "%" + key + "%");
            rs = ps.executeQuery();
            if (rs.next()) {
                total = rs.getInt(1);
            }
            JDBCUtil.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            JDBCUtil.rollback();
        } finally {
            JDBCUtil.closeAll(connection, ps, rs);
        }
        return total;
    }

    @Override
    public boolean addInfo(BorrowInfo borrowInfo) {
        int result = 0;
        try{
            Connection connection = JDBCUtil.getConnection_2();
            String sql = "insert into borrowinfo (bookid, bookname, reader, borrowdate, returndate, isreturn) " +
                    "values (?,?,?,?,?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            int bookId = new BookServiceImpl().getIdByName(borrowInfo.getBookName());
            if(bookId < 0)
                return false;
            preparedStatement.setInt(1, bookId);
            preparedStatement.setString(2, borrowInfo.getBookName());
            preparedStatement.setString(3, borrowInfo.getReader());
            preparedStatement.setDate(4, borrowInfo.getBorrowDate());
            preparedStatement.setDate(5, borrowInfo.getReturnDate());
            preparedStatement.setInt(6, borrowInfo.getIsReturn());
            result = preparedStatement.executeUpdate();
            JDBCUtil.closePreparedStatement(preparedStatement);
            JDBCUtil.closeConnection(connection);

        }catch (Exception e){
            e.printStackTrace();
        }
        return result > 0;

//        Connection connection = JDBCUtil.getConnection();
//        int result = 0;
//        PreparedStatement ps = null;
//        String sql = "insert into borrowinfo (bookid, bookname, reader, borrowdate, returndate, isreturn) " +
//                "values (?,?,?,?,?,?)";
//        try {
//            JDBCUtil.begin();
//            ps = Objects.requireNonNull(connection).prepareStatement(sql);
//            int bookId = new BookServiceImpl().getIdByName(borrowInfo.getBookName());
//            if(bookId < 0)
//                return false;
//            ps.setInt(1, bookId);
//            ps.setString(2, borrowInfo.getBookName());
//            ps.setString(3, borrowInfo.getReader());
//            ps.setDate(4, borrowInfo.getBorrowDate());
//            ps.setDate(5, borrowInfo.getReturnDate());
//            ps.setInt(6, borrowInfo.getIsReturn());
//            result = ps.executeUpdate();
//            JDBCUtil.commit();
//        } catch (SQLException e) {
//            e.printStackTrace();
//            JDBCUtil.rollback();
//        } finally {
//            JDBCUtil.closeAll(connection, ps, null);
//        }
//        return result > 0;
    }

    @Override
    public boolean removeInfo(int borrowId) {
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement ps = null;
        String sql = "delete from borrowinfo where borrowid=?";
        int result = 0;
        try {
            JDBCUtil.begin();
            ps = Objects.requireNonNull(connection).prepareStatement(sql);
            ps.setInt(1, borrowId);
            result = ps.executeUpdate();
            JDBCUtil.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            JDBCUtil.rollback();
        } finally {
            JDBCUtil.closeAll(connection, ps, null);
        }
        return result > 0;
    }

    @Override
    public boolean updateInfo(BorrowInfo borrowInfo) {
        int result = 0;
        try{
            Connection connection = JDBCUtil.getConnection_2();
            String sql = "update borrowinfo set bookid=?, bookname=?, reader=?, borrowdate=?, returndate=?, isreturn=? " +
                    "where borrowid=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            int bookId = new BookServiceImpl().getIdByName(borrowInfo.getBookName());
            if(bookId < 0)
                return false;
            preparedStatement.setInt(1, bookId);
            preparedStatement.setString(2, borrowInfo.getBookName());
            preparedStatement.setString(3, borrowInfo.getReader());
            preparedStatement.setDate(4, borrowInfo.getBorrowDate());
            preparedStatement.setDate(5, borrowInfo.getReturnDate());
            preparedStatement.setInt(6, borrowInfo.getIsReturn());
            preparedStatement.setInt(7, borrowInfo.getBorrowId());
            result = preparedStatement.executeUpdate();
            JDBCUtil.closePreparedStatement(preparedStatement);
            JDBCUtil.closeConnection(connection);

        }catch (Exception e){
            e.printStackTrace();
        }
        return result > 0;

//        Connection connection = JDBCUtil.getConnection();
//        PreparedStatement ps = null;
//
//        try {
//            JDBCUtil.begin();
//            ps = Objects.requireNonNull(connection).prepareStatement(sql);
//            ps.setInt(1, borrowInfo.getBookId());
//            ps.setString(2, borrowInfo.getBookName());
//            ps.setString(3, borrowInfo.getReader());
//            ps.setDate(4, borrowInfo.getBorrowDate());
//            ps.setDate(5, borrowInfo.getReturnDate());
//            ps.setInt(6, borrowInfo.getIsReturn());
//            ps.setInt(7, borrowInfo.getBorrowId());
//            ps.executeUpdate();
//            JDBCUtil.commit();
//
//        } catch (SQLException e) {
//            e.printStackTrace();
//            JDBCUtil.rollback();
//        } finally {
//            JDBCUtil.closeAll(connection, ps, null);
//        }
    }

    @Override
    public BorrowInfo getByBorrowId(int borrowId) {
        BorrowInfo borrowInfo = new BorrowInfo();
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from borrowinfo where borrowid=?";
        try {
            JDBCUtil.begin();
            ps = Objects.requireNonNull(connection).prepareStatement(sql);
            ps.setInt(1, borrowId);
            rs = ps.executeQuery();
            if (rs.next()) {
                borrowInfo.setBorrowId(borrowId);
                borrowInfo.setBookId(rs.getInt(2));
                borrowInfo.setBookName(rs.getString(3));
                borrowInfo.setReader(rs.getString(4));
                borrowInfo.setBorrowDate(rs.getDate(5));
                borrowInfo.setReturnDate(rs.getDate(6));
                borrowInfo.setIsReturn(rs.getInt(7));
            }
            JDBCUtil.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            JDBCUtil.rollback();
        } finally {
            JDBCUtil.closeAll(connection, ps, rs);
        }
        return borrowInfo;
    }

    @Override
    public List<BorrowInfo> searchListByKey(String key) {
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from borrowinfo " +
                "where bookname like ? or reader like ?";
        if (key == null) {
            key = "";
        }
        List<BorrowInfo> borrowInfos = new ArrayList<>();
        try {
            JDBCUtil.begin();
            ps = Objects.requireNonNull(connection).prepareStatement(sql);
            ps.setString(1, "%" + key + "%");
            ps.setString(2, "%" + key + "%");
            rs = ps.executeQuery();
//            BorrowInfo borrowInfo = new BorrowInfo();
            while (rs.next()) {
//                borrowInfo.setBorrowId();
//                borrowInfo.setBookId();
//                borrowInfo.setBookName();
//                borrowInfo.setReader();
//                borrowInfo.setBorrowDate();
//                borrowInfo.setReturnDate();
                BorrowInfo borrowInfo = new BorrowInfo(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4), rs.getDate(5), rs.getDate(6), rs.getInt(7));
                borrowInfos.add(borrowInfo);
            }
            JDBCUtil.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            JDBCUtil.rollback();
        } finally {
            JDBCUtil.closeAll(connection, ps, rs);
        }
        return borrowInfos;
    }

//    public static void main(String[] args) {
//        BorrowInfoDao borrowInfoDao = new BorrowInfoDaoImpl();
//        Date date = new Date(1234, 1, 2);
//        Date date1 = new Date(2345, 3, 4);
//        BorrowInfo borrowInfo = new BorrowInfo(1, 2, "bookname", "reader", date, date1, 1);
//        BorrowInfo borrowInfo = borrowInfoDao.getByBorrowId(4);
//        System.out.println(borrowInfo.getBorrowDate());
//    }
}
